-- This file assumes that you have executed 05_certsignserver.sql.
SET NOCOUNT ON
USE master
go

-- To be able to sign a procedure in the PermTest database with the
-- DemoServerCert, we need to export it from master to PermTest.
-- First we do this with BACKUP CERTIFICATE. We must specify a password
-- to protect the private key. 
BACKUP CERTIFICATE DemoServerCert 
    TO FILE='C:\temp\DemoServerCert.cer'
WITH PRIVATE KEY
   (FILE = 'C:\temp\DemoServerCert.pvk',
    DECRYPTION BY PASSWORD = 'While My Guitar Gently Weeps',
    ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps')

-- Move over to the PermTest database.
USE PermTest
go
-- Import the certificate from the files. Note that we can select a new 
-- password for the cert in this database.
CREATE CERTIFICATE DemoServerCert 
    FROM FILE='C:\temp\DemoServerCert.cer'
WITH PRIVATE KEY
    (FILE = 'C:\temp\DemoServerCert.pvk',
     DECRYPTION BY PASSWORD = 'While My Guitar Gently Weeps',
     ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps')

-- Delete the certificate from disk, so that next export does not fail.
EXEC xp_cmdshell 'DEL C:\temp\DemoServerCert.*', 'no_output'
go
-- We need the view for login-token information in PermTest as well.
-- Create the view with logintokeninfo also in PermTest
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login, 
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER 
   FROM   sys.login_token
go
-- Create a database-local version of sp_showusersindb to be more supported.
CREATE PROCEDURE ShowSessions AS
   SELECT * FROM logintokeninfo

   IF is_srvrolemember('SuperUsers') = 1 OR
      has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
     SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
     RAISERROR('You are not permitted to run this procedure', 16, 1)
go
ADD SIGNATURE TO ShowSessions BY CERTIFICATE DemoServerCert 
    WITH PASSWORD = 'While My Guitar Gently Weeps'
go

-- Play our super user.
EXECUTE AS LOGIN = 'PermTestSuper'
go
EXECUTE ShowSessions
go
REVERT

-- Drop signature and certificate for now.
DROP SIGNATURE FROM ShowSessions BY CERTIFICATE DemoServerCert
DROP CERTIFICATE DemoServerCert

-- Back to master for the second way to export. Works on SQL 2012 and up.
USE master
go
-- Here is the concept:
DECLARE @public_key varbinary(MAX) = 
            certencoded(cert_id('DemoServerCert')),
        @private_key varbinary(MAX) = 
            certprivatekey(cert_id('DemoServerCert'), 
                           'While My Guitar Gently Weeps', 
                           'While My Guitar Gently Weeps')
-- Change database.
USE PermTest
-- Alas, this syntax is not valid - the binary values must be literal.
CREATE CERTIFICATE DemoServerCert
FROM BINARY = @public_key
WITH PRIVATE KEY
   (BINARY = @private_key,
    DECRYPTION BY PASSWORD = 'While My Guitar Gently Weeps',
    ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps')
go
-- We need to use dynamic SQL, sigh.
USE master
go
DECLARE @public_key varbinary(MAX) = 
            certencoded(cert_id('DemoServerCert')),
        @private_key varbinary(MAX) = 
            certprivatekey(cert_id('DemoServerCert'), 
                           'While My Guitar Gently Weeps', 
                           'While My Guitar Gently Weeps'),
        @sql nvarchar(MAX)

-- Add SELECT so if the SQL comes out blank, you can see which key you did not retrieve.
--SELECT @public_key, @private_key

SELECT @sql = 
   'CREATE CERTIFICATE DemoServerCert
    FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
    WITH PRIVATE KEY
        (BINARY = ' + convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''While My Guitar Gently Weeps'',
         ENCRYPTION BY PASSWORD = ''While My Guitar Gently Weeps'')'

PRINT  @sql
-- Execute the batch in PermTest
EXEC PermTest..sp_executesql @sql
go

-- Move to PermTest again
USE PermTest
go
-- Sign the procedure a new.
ADD SIGNATURE TO ShowSessions BY CERTIFICATE DemoServerCert
                 WITH PASSWORD = 'While My Guitar Gently Weeps'
go
-- Drop the private key.
ALTER CERTIFICATE DemoServerCert REMOVE PRIVATE KEY
go
-- The super user can run the procedure.
EXECUTE AS LOGIN = 'PermTestSuper'
go
EXECUTE ShowSessions
go
REVERT

